<%@ page pageEncoding="UTF-8" import="com.lowagie.text.*,
                 com.lowagie.text.pdf.*,
                 java.io.*,
                 java.sql.*"%><%
    response.reset();
    response.setContentType("application/pdf");
%><%
    String tableColStr = request.getParameter("tableColStr");
    String tabWidth = request.getParameter("tabWidth");
    String sql = request.getParameter("sql");
    String bw = request.getParameter("bw");
	  if (bw.equals("ie")) {
	      tableColStr = new String(tableColStr.getBytes("ISO8859-1"), "GB2312");
	      sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
	  } else {
	      tableColStr = new String(tableColStr.getBytes("ISO8859-1"), "UTF-8");
	      sql = new String(sql.getBytes("ISO8859-1"), "UTF-8");
	  }

%><%
class colGroupingTablePDF {
    private ByteArrayOutputStream buffPdf;
    private PdfPTable table, tableColumn;
    private PdfPCell tableHeader, tableHeader1;
    private BaseFont bfChn;
    private Font tabFont, tabH1Font, tabH2Font;
//    private currentRowCell

    private PdfPCell tableColumn(String[] _columnStr, float[] _columnWidth, float _Width) {
        PdfPTable tcol = new PdfPTable(_columnStr.length);
        String groupName = "";
        int gIndexFirst = -1;
        String[] gcolStr;
        float[] gcolWidth;
        try {
            if (_columnWidth != null) {
                tcol.setWidths(_columnWidth);
            }
        } catch (DocumentException e) {
            System.err.println(e.getMessage());
        }
        tcol.getDefaultCell().setHorizontalAlignment(Element.ALIGN_CENTER);
        tcol.getDefaultCell().setVerticalAlignment(Element.ALIGN_MIDDLE);
        for (int i = 0; i < _columnStr.length; ++i) {
            if (_columnStr[i].indexOf("|") == -1) {
            		tcol.addCell(new Paragraph(_columnStr[i], tabH2Font));
            } else {
                if (gIndexFirst == -1) {
                    gIndexFirst = i;
                    groupName = _columnStr[i].substring(0, _columnStr[i].indexOf("|"));
                }
                if ((i + 1) >= _columnStr.length || _columnStr[i + 1].indexOf("|") == -1 || !groupName.equals(_columnStr[i + 1].substring(0, _columnStr[i + 1].indexOf("|")))) {
                    gcolStr = new String[i - gIndexFirst + 1];
                    gcolWidth = new float[i - gIndexFirst + 1];
                    for (int j = gIndexFirst, x = 0; j <= i; ++j, ++x) {
                        gcolStr[x] = _columnStr[j].substring(_columnStr[j].indexOf("|") + 1, _columnStr[j].length());
                        gcolWidth[x] = _columnWidth[j] * (table.getWidthPercentage() / _Width);
                    }
                    tcol.addCell(groupColumns(groupName, gcolStr, gcolWidth));
                    gIndexFirst = -1;
                    groupName = "";
                }
            }
        }
        PdfPCell result = new PdfPCell(tcol);
        result.setColspan(_columnStr.length);
        return result;
    }

    private PdfPCell groupColumns(String _groupName, String[] _columnStr, float[] _columnWidth) {
        PdfPTable gcol = new PdfPTable(_columnStr.length);
        float width = 0;
        if (_columnWidth != null) {
            try {
                gcol.setWidths(_columnWidth);
            } catch (DocumentException e) {
                System.err.println(e.getMessage());
            }
        }
        gcol.getDefaultCell().setHorizontalAlignment(Element.ALIGN_CENTER);
        gcol.getDefaultCell().setVerticalAlignment(Element.ALIGN_MIDDLE);
        PdfPCell gcell;
        gcell = new PdfPCell(new Paragraph(_groupName, tabH2Font));
        gcell.setColspan(_columnStr.length);
        gcell.setHorizontalAlignment(Element.ALIGN_CENTER);
        gcell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        gcol.addCell(gcell);
        for (int i = 0; i < _columnWidth.length; ++i) {
            width += _columnWidth[i];
        }
        gcol.addCell(tableColumn(_columnStr, _columnWidth, width));
        PdfPCell result = new PdfPCell(gcol);
        result.setColspan(_columnStr.length);
        return result;
    }

    private int[] StringToIntArray(String _intArrayStr ,String _regex){
        String strArray[] = _intArrayStr.split(_regex);
        int length = strArray.length;
        int intArray[] = new int[length];
        for(int i = 0;i<length;++i){
            try{
                intArray[i] = Integer.parseInt(strArray[i]);
            }catch(NumberFormatException ex){
                intArray[i] = 0;
            }
        }
        return intArray;
    }
    private float[] StringToFloatArray(String _intArrayStr ,String _regex){
        String strArray[] = _intArrayStr.split(_regex);
        int length = strArray.length;
        float floatArray[] = new float[length];
        for(int i = 0;i<length;++i){
            try{
                floatArray[i] = Float.valueOf(strArray[i]);
            }catch(NumberFormatException ex){
                floatArray[i] = 0;
            }
        }
        return floatArray;
    }

    private void createEmptyRow() {
        PdfPCell cell = new PdfPCell();
        for (int i = 0; i < tableColumn.getNumberOfColumns(); ++i) {
            table.addCell(cell);
        }
    }

    public void setBaseFont(String _fontName, String _encoding) {
        try {
            bfChn = BaseFont.createFont(_fontName, _encoding, BaseFont.NOT_EMBEDDED);
        } catch (DocumentException e) {
            System.err.println(e.getMessage());
        } catch (IOException e) {
            System.err.println(e.getMessage());
        }
    }

    public void setRowCell(int _columnIndex, String _cellValue, float _miniHeight, int _style, float _cellGray) {
        if (_columnIndex == 0) createEmptyRow();
        table.getRow(table.size() - 1).getCells()[_columnIndex] = new PdfPCell(new Paragraph(_cellValue, tabFont));
        table.getRow(table.size() - 1).getCells()[_columnIndex].setMinimumHeight(_miniHeight);
        table.getRow(table.size() - 1).getCells()[_columnIndex].setGrayFill(_cellGray);
        table.getRow(table.size() - 1).getCells()[_columnIndex].setHorizontalAlignment(_style == 0 ? Element.ALIGN_LEFT : _style == 1 ? Element.ALIGN_CENTER : Element.ALIGN_RIGHT);
        table.getRow(table.size() - 1).getCells()[_columnIndex].setVerticalAlignment(Element.ALIGN_MIDDLE);
    }

    public ByteArrayOutputStream OutputPdfStream() {
        try {
            Document docPdf = new Document();
            buffPdf = new ByteArrayOutputStream();
            PdfWriter writer = PdfWriter.getInstance(docPdf, buffPdf);
            writer.setPdfVersion(PdfWriter.VERSION_1_5);
            docPdf.open();
            PdfAction action = PdfAction.gotoLocalPage(1, new PdfDestination(PdfDestination.XYZ, 0, 10000, 1), writer);
            writer.setOpenAction(action);
            docPdf.add(table);
            docPdf.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
        return buffPdf;
    }

    public ByteArrayOutputStream OutputPdfStream(int pageStyle) {
        try {
            Document docPdf;
            if(pageStyle == 0){
                docPdf = new Document(PageSize.A4.rotate());
            }else{
                docPdf = new Document(PageSize.A4);
            }
            buffPdf = new ByteArrayOutputStream();
            PdfWriter writer = PdfWriter.getInstance(docPdf, buffPdf);
            writer.setPdfVersion(PdfWriter.VERSION_1_5);
            docPdf.open();
            PdfAction action = PdfAction.gotoLocalPage(1, new PdfDestination(PdfDestination.XYZ, 0, 10000, 1), writer);
            writer.setOpenAction(action);
            docPdf.add(table);
            docPdf.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
        return buffPdf;
    }

    public colGroupingTablePDF(String[] _columnStr, float[] _columnWidth, float _widthPercentage, String _hdStr) {
        table = new PdfPTable(_columnStr.length);
        table.setWidthPercentage(_widthPercentage);
        table.setHeaderRows(2);
        table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_CENTER);
        table.getDefaultCell().setVerticalAlignment(Element.ALIGN_MIDDLE);
        try {
            table.setWidths(_columnWidth);
            tableColumn = new PdfPTable(_columnStr.length);
            tableColumn.setWidths(_columnWidth);
            bfChn = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);
            tabFont = new Font(bfChn, 9);
            tabH1Font = new Font(bfChn, 12, Font.BOLD);
            tabH2Font = new Font(bfChn, 9, Font.BOLD);
        } catch (DocumentException e) {
            System.err.println(e.getMessage());
        } catch (IOException e) {
            System.err.println(e.getMessage());
        }
        tableHeader = new PdfPCell(new Paragraph(_hdStr, tabH1Font));
        tableHeader.setGrayFill(0.7f);
        tableHeader.setColspan(_columnStr.length);
        tableHeader.setMinimumHeight(30);
        tableHeader.setVerticalAlignment(Element.ALIGN_MIDDLE);
        table.addCell(tableHeader);
        tableHeader1 = new PdfPCell(tableColumn(_columnStr, _columnWidth, 100));
        tableHeader1.setColspan(_columnStr.length);
        tableHeader1.setMinimumHeight(25);
        table.addCell(tableHeader1);
    }

    public colGroupingTablePDF(String _columnStr, String _columnWidth, float _widthPercentage, String _hdStr ,String _regex) {
        String __columnStr[] = _columnStr.split(_regex);
        float __columnWidth[] = StringToFloatArray(_columnWidth,_regex);
        table = new PdfPTable(__columnStr.length);
        table.setWidthPercentage(_widthPercentage);
        table.setHeaderRows(2);
        table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_CENTER);
        table.getDefaultCell().setVerticalAlignment(Element.ALIGN_MIDDLE);
        try {
            table.setWidths(__columnWidth);
            tableColumn = new PdfPTable(__columnStr.length);
            tableColumn.setWidths(__columnWidth);
            bfChn = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);
            tabFont = new Font(bfChn, 9);
            tabH1Font = new Font(bfChn, 12, Font.BOLD);
            tabH2Font = new Font(bfChn, 9, Font.BOLD);
        } catch (DocumentException e) {
            System.err.println(e.getMessage());
        } catch (IOException e) {
            System.err.println(e.getMessage());
        }
        tableHeader = new PdfPCell(new Paragraph(_hdStr, tabH1Font));
        tableHeader.setGrayFill(0.7f);
        tableHeader.setColspan(__columnStr.length);
        tableHeader.setMinimumHeight(30);
        tableHeader.setVerticalAlignment(Element.ALIGN_MIDDLE);
        table.addCell(tableHeader);
        tableHeader1 = new PdfPCell(tableColumn(__columnStr, __columnWidth, 100));
        tableHeader1.setColspan(__columnStr.length);
        tableHeader1.setMinimumHeight(25);
        table.addCell(tableHeader1);
    }
}
%><%
    // = null;
    Connection conn = null;
    ResultSet resultSet = null;
    Statement statement = null;
    String projectId = request.getParameter("projectId");
    int numberOfColumns = 1;
    try {
				Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");// SqlDriver
				conn = DriverManager.getConnection(
						"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=LC_GZMY",
						"sa",
						"zaq1");
        statement = conn.createStatement();
        resultSet = statement.executeQuery(sql);
        ResultSetMetaData metaData = resultSet.getMetaData();
        numberOfColumns = metaData.getColumnCount();
        colGroupingTablePDF ptrPdf = new colGroupingTablePDF(tableColStr, tabWidth, 100, "测试数据" ,":");
        boolean lastRow = false;
        float gray ,height ;
        while (resultSet != null && resultSet.next()) {
            if (resultSet.getObject(1) == null || resultSet.getObject(1).toString().equals("")) {
                lastRow = true;
            }
            for (int i = 1; i <= numberOfColumns; i++) {
                Object value = resultSet.getObject(i);
                if (value == null) value = "";
                if (lastRow) {
                    gray = 0.9f;
                    height = 20f;
                } else {
                    gray = 1.0f;
                    height = 25f;
                }
                if ( i>=4 ) {
                    ptrPdf.setRowCell(i - 1, value.toString(), height, 2, gray);
                } else {
                    ptrPdf.setRowCell(i - 1, value.toString(), height, 0, gray);
                }
            }
        }
        DataOutput dataOutput = new DataOutputStream(response.getOutputStream());
        ByteArrayOutputStream buffer = ptrPdf.OutputPdfStream();//设置A4纸横排竖排
        byte[] bytes = buffer.toByteArray();
        int bArrayLength = bytes.length;
        response.setContentLength(bArrayLength);
        for (int i = 0; i < bArrayLength; i++) {
            dataOutput.writeByte(bytes[i]);
        }
    }
    catch (Exception e) {
        System.err.println(e.getMessage());
        e.printStackTrace();
    } finally {
        try {
            resultSet.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            statement.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
%>